package vg.model;
import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import vg.core.VisualGraph;
import vg.core.storableGraph.StorableAttribute;
import vg.core.storableGraph.StorableEdge;
import vg.core.storableGraph.StorableGraph;
import vg.core.storableGraph.StorableSubGraph;
import vg.core.storableGraph.StorableVertex;
import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteException;
import com.almworks.sqlite4java.SQLiteJob;
import com.almworks.sqlite4java.SQLiteQueue;
import com.almworks.sqlite4java.SQLiteStatement;
/**
* This class realizes work with data base.
* @author tzolotuhin
*/
public class SQLite4JavaDataBase {
private SQLiteQueue currConnection;
//-------------------------------------------------------------------------
public SQLite4JavaDataBase() throws Throwable {
File f;
Logger.getLogger("com.almworks.sqlite4java").setLevel(Level.WARNING);
File dbfile = File.createTempFile("tmpdb", ".db", new File("data/db/"));
this.currConnection = new SQLiteQueue(dbfile);
dbfile.deleteOnExit();
//create structure of data base
this.currConnection.start();
SQLiteJob<Void> job = currConnection.execute(new SQLiteJob<Void>() {
protected Void job(SQLiteConnection connection) throws Throwable {
SQLiteStatement st = null;
st = connection.prepare("pragma synchronous=OFF;");//very plus to speed
st.stepThrough();
st.dispose();
st = connection.prepare("begin;");
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists com_graph_subgraph;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists com_subgraph_edge;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists com_subgraph_vertex;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists com_edge_attribute;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists com_vertex_attribute;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists graph;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists subgraph;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists attribute;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists vertex;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("drop table if exists edge;",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table vertex (db_id INTEGER PRIMARY KEY, id VARCHAR, db_id_inner_graph INTEGER);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table edge (db_id INTEGER PRIMARY KEY, id VARCHAR, db_id_source INT, db_id_target INT);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table attribute (db_id INTEGER PRIMARY KEY, name VARCHAR, value VARCHAR, value_type VARCHAR);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table subgraph (db_id INTEGER PRIMARY KEY, id VARCHAR, name VARCHAR, directed BOOLEAN);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table graph (db_id INTEGER PRIMARY KEY, root_key INTEGER, name VARCHAR);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table com_vertex_attribute (db_id INTEGER PRIMARY KEY autoincrement, db_id_vertex INTEGER, db_id_attribute INTEGER);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table com_edge_attribute (db_id INTEGER PRIMARY KEY autoincrement, db_id_edge INTEGER, db_id_attribute INTEGER);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table com_subgraph_vertex (db_id INTEGER PRIMARY KEY autoincrement, db_id_subgraph INTEGER, db_id_vertex INTEGER);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table com_subgraph_edge (db_id INTEGER PRIMARY KEY autoincrement, db_id_subgraph INTEGER, db_id_edge INTEGER);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create table com_graph_subgraph (db_id INTEGER PRIMARY KEY autoincrement, db_id_graph INTEGER, db_id_subgraph INTEGER);",false);
st.stepThrough();
st.dispose();
//Indexes creating
st = connection.prepare("create index Idx_vertex on vertex(db_id_inner_graph );",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_edge on edge(db_id_source, db_id_target);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_attribute on attribute(name);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_graph on graph(root_key);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_com_vertex_attribute on com_vertex_attribute(db_id_vertex, db_id_attribute);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_com_edge_attribute on com_edge_attribute (db_id_edge, db_id_attribute);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_com_subgraph_vertex on com_subgraph_vertex(db_id_subgraph, db_id_vertex);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_com_subgraph_edge on com_subgraph_edge(db_id_subgraph, db_id_edge);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("create index Idx_com_graph_subgraph on com_graph_subgraph (db_id_graph, db_id_subgraph);",false);
st.stepThrough();
st.dispose();
st = connection.prepare("commit;");
st.stepThrough();
st.dispose();
return null;
}
});
job.complete();
//check errors
Throwable ex = job.getError();
if (ex != null) {
throw(ex);
}
}
/**
* This method returns graph name.
* @param graphId - index of graph in data base.
* @return - graph name.
*/
public synchronized String getGraphName(final int graphId) {
SQLiteJob<String> job = this.currConnection.execute(new SQLiteJob<String>() {
protected String job(SQLiteConnection connection) throws Throwable {
SQLiteStatement currStatement = null;
String request = "select * " +
"from graph s1 " +
"where s1.db_id = " + Integer.valueOf(graphId).toString() + ";";
currStatement = connection.prepare(request,false);
String name = "";
if (currStatement.step())
if (currStatement.hasRow())
{
name = currStatement.columnString(2);
}
currStatement.dispose();
return name;
}
});
String result = job.complete();
Throwable ex = job.getError();
if (ex != null)
{
VisualGraph.log.printException(ex);
return(null);
}
return(result);
}
/**
* This methods adds new graph(structure of graph) to data base.
* @param sg - storable graph.
* @param subgraphIDs - subgraphs of the graph.
*/
public synchronized void addStorableGraph(final StorableGraph sg, final ArrayList<Integer> subgraphIDs) {
if(sg == null) return;
try {
SQLiteJob<Object> job = this.currConnection.execute(new SQLiteJob<Object>() {
protected Object job(SQLiteConnection connection) throws Throwable {
SQLiteStatement currStatement = null;
currStatement = connection.prepare("BEGIN;");
currStatement.stepThrough();
currStatement.dispose();
return null;
}
});
job.complete();
if (job.getError() != null)
{
throw(job.getError());
}
//add graph--------------------------
job = this.currConnection.execute(new SQLiteJob<Object>() {
protected Object job(SQLiteConnection connection) throws Throwable {
StringBuffer request = new StringBuffer(1024 * 4);
request.append("insert into graph values(");
request.append(sg.getStorableId());
if(sg.getRootSubGraphId() == null) {
request.append(", -1,");
} else {
request.append(", ");
request.append(sg.getRootSubGraphId());
request.append(",");
}
if(sg.getName() == null) {
request.append(" null);");
} else {
request.append("'");
request.append(sg.getName());
request.append("');");
}
SQLiteStatement currStatement = connection.prepare(request.toString(),false);
currStatement.stepThrough();
currStatement.dispose();
return(null);
}
});
job.complete();
if (job.getError() != null)
{
throw(job.getError());
}
//add subgraphs----------------------
job = this.currConnection.execute(new SQLiteJob<Object>() {
@Override
protected Object job(SQLiteConnection connection) throws Throwable {
for(Integer id : subgraphIDs) {
StringBuffer request = new StringBuffer(1024 * 4);
request.append("insert into com_graph_subgraph values(?, ");
request.append(sg.getStorableId());
request.append(", ");
request.append(id);
request.append(");");
SQLiteStatement currStatement = connection.prepare(request.toString(),false);
currStatement.stepThrough();
currStatement.dispose();
}
return(null);
}
});
job.complete();
if (job.getError() != null)
{
throw(job.getError());
}
job = this.currConnection.execute(new SQLiteJob<Object>() {
protected Object job(SQLiteConnection connection) throws Throwable {
SQLiteStatement currStatement = null;
currStatement = connection.prepare("COMMIT;");
currStatement.stepThrough();
currStatement.dispose();
return null;
}
});
job.complete();
if (job.getError() != null)
{
throw(job.getError());
}
} catch (Throwable ex) {
VisualGraph.log.printException(ex);
VisualGraph.windowMessage.warningMessage(ex.getMessage(), "Adding to database");
}
}
/**
* This method adds new subgraph to data base.
* @param ssg - storable subgraph.
*/
public synchronized void addStorableSubGraph(final StorableSubGraph ssg) {
if(ssg == null) return;
try {
SQLiteJob<Object> job = this.currConnection.execute(new SQLiteJob<Object>() {
@Override
protected Object job(SQLiteConnection connection) throws Throwable {
SQLiteStatement currStatement = null;
currStatement = connection.prepare("BEGIN;");
currStatement.stepThrough();
currStatement.dispose();
//add graph--------------------------
StringBuffer request = new StringBuffer(10*1024);
request.append("insert into subgraph values(");
request.append(ssg.getStorableId());
if(ssg.getId() == null) {
request.append(", null");
} else {
request.append(", '");
request.append(ssg.getId());
request.append("'");
}
if(ssg.getName() == null) {
request.append(", null");
} else {
request.append(", '");
request.append(ssg.getName());
request.append("'");
}
if(ssg.isDirected()) {
request.append(", 'true');");
} else {
request.append(", 'false');");
}
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
//add all vertexes---------------
for(StorableVertex bufVertex : ssg.getVertices()) {
request.append("insert into vertex values(");
request.append(bufVertex.getStorableId());
if(bufVertex.getId() == null) {
request.append(", null");
} else {
request.append(", '");
request.append(bufVertex.getId());
request.append("'");
}
if(bufVertex.getInnerGraph() == null) {
request.append(", null);");
} else {
request.append(", ");
request.append(bufVertex.getInnerGraph());
request.append(");");
}
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
//add all attributes---------
for(StorableAttribute bufAttribute : bufVertex.getAttributes()) {
request.append("insert into attribute values(");
request.append(bufAttribute.getStorableId());
if(bufAttribute.getName() == null) {
request.append(", null");
} else {
request.append(", '");
request.append(bufAttribute.getName());
request.append("'");
}
if(bufAttribute.getValue() == null) {
request.append(", null);");
} else {
request.append(", '");
request.append(bufAttribute.getValue());
request.append("'");
}
if(bufAttribute.getType() == null) {
request.append(", null);");
} else {
request.append(", '");
request.append(bufAttribute.getType());
request.append("');");
}
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
//add composition between vertex and attribute
request.append("insert into com_vertex_attribute values(?, ");
request.append(bufVertex.getStorableId());
request.append(", ");
request.append(bufAttribute.getStorableId());
request.append(");");
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
}
//add composition between subgraph and vertex
request.append("insert into com_subgraph_vertex values(?, ");
request.append(ssg.getStorableId());
request.append(", ");
request.append(bufVertex.getStorableId());
request.append(");");
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
}
//add all edges------------------
for(StorableEdge bufEdge : ssg.getEdges()) {
request.append("insert into edge values(");
request.append(bufEdge.getStorableId());
if(bufEdge.getId() == null) {
request.append(", null");
} else {
request.append(", '");
request.append(bufEdge.getId());
request.append("'");
}
request.append(", ");
request.append(bufEdge.getStorableSource().getStorableId());
request.append(", ");
request.append(bufEdge.getStorableTarget().getStorableId());
request.append(");");
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
//add all attributes---------
for(StorableAttribute bufAttribute : bufEdge.getStorableAttributes()) {
request.append("insert into attribute values(");
request.append(bufAttribute.getStorableId());
if(bufAttribute.getName() == null) {
request.append(", null");
} else {
request.append(", '");
request.append(bufAttribute.getName());
request.append("'");
}
if(bufAttribute.getValue() == null) {
request.append(", null);");
} else {
request.append(", '");
request.append(bufAttribute.getValue());
request.append("'");
}
if(bufAttribute.getType() == null) {
request.append(", null);");
} else {
request.append(", '");
request.append(bufAttribute.getType());
request.append("');");
}
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
//add composition between edge and attribute
request.append("insert into com_edge_attribute values(?, ");
request.append(bufEdge.getStorableId());
request.append(", ");
request.append(bufAttribute.getStorableId());
request.append(");");
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
}
//add composition between subgraph and edge
request.append("insert into com_subgraph_edge values(?, ");
request.append(ssg.getStorableId());
request.append(", ");
request.append(bufEdge.getStorableId());
request.append(");");
currStatement = connection.prepare(request.toString(),false);
request.setLength(0);
currStatement.stepThrough();
currStatement.dispose();
}
currStatement = connection.prepare("COMMIT;");
currStatement.stepThrough();
currStatement.dispose();
return null;
}
});
job.complete();
if (job.getError() != null)
{
throw(job.getError());
}
this.currConnection.flush();
} catch (Throwable ex) {
VisualGraph.log.printException(ex);
VisualGraph.windowMessage.warningMessage(ex.getMessage(), "Adding to database");
}
}
/**
* This method returns storable graph.
* @param graphId - id of storable graph in data base.
* @return storable graph.
*/
public synchronized StorableGraph getStorableGraph(final int graphId) {
SQLiteJob<StorableGraph> job = this.currConnection.execute(new SQLiteJob<StorableGraph>() {
protected StorableGraph job(SQLiteConnection connection) throws Throwable {
String request = "select * " +
"from com_graph_subgraph s1 " +
"where s1.db_id_graph = " + Integer.valueOf(graphId).toString() + ";";
SQLiteStatement currStatement = connection.prepare(request,false);
ArrayList<StorableSubGraph>subGraphs = new ArrayList<StorableSubGraph>();
while(currStatement.step()) {
//getting of subgraph------------
int id_subgraph = currStatement.columnInt(2);
boolean directed = false;
String id = null;
String name = null;
SQLiteStatement subgraphStatement = null;
request = "select *" +
"from subgraph s1 " +
"where s1.db_id = " + Integer.valueOf(id_subgraph).toString() + ";";
subgraphStatement = connection.prepare(request,false);
if(subgraphStatement.step()) {
String buf = subgraphStatement.columnString(3);
if(buf != null && buf.equals("true")) {
directed = true;
} else {
directed = false;
}
id = subgraphStatement.columnString(1);
name = subgraphStatement.columnString(2);
}
subgraphStatement.dispose();
//vertexes-----------------------
ArrayList<StorableVertex>listVertex = new ArrayList<StorableVertex>();
SQLiteStatement vertexStatement = null;
request = "select s2.db_id, s2.id, s2.db_id_inner_graph " +
"from com_subgraph_vertex s1, vertex s2 " +
"where s1.db_id_subgraph = " + Integer.valueOf(id_subgraph).toString() + " and s2.db_id = s1.db_id_vertex;";
vertexStatement = connection.prepare(request,false);
while(vertexStatement.step()) {
int db_id_vertex = vertexStatement.columnInt(0);
String id_vertex = vertexStatement.columnString(1);
Integer db_id_innder_graph = (Integer)vertexStatement.columnValue(2);
StorableVertex v = new StorableVertex(db_id_vertex, id_vertex);
v.setInnerGraph(db_id_innder_graph);
listVertex.add(v);
//attributes-----------------
SQLiteStatement attrStatement = null;
request = "select s2.db_id, s2.name, s2.value, s2.value_type " +
"from com_vertex_attribute s1, attribute s2 " +
"where s1.db_id_vertex = " + Integer.valueOf(db_id_vertex).toString() + " and s2.db_id = s1.db_id_attribute;";
attrStatement = connection.prepare(request,false);
while(attrStatement.step()) {
int db_id_attr = attrStatement.columnInt(0);
String db_name = attrStatement.columnString(1);
String db_value = attrStatement.columnString(2);
String db_type = attrStatement.columnString(3);
v.addStorableAttribute(new StorableAttribute(db_id_attr, db_name, db_value, db_type));
}
attrStatement.dispose();
}
vertexStatement.dispose();
//edges--------------------------
ArrayList<StorableEdge>listEdge = new ArrayList<StorableEdge>();
SQLiteStatement edgeStatement = null;
request = "select s2.db_id, s2.id, s2.db_id_source, db_id_target " +
"from com_subgraph_edge s1, edge s2 " +
"where s1.db_id_subgraph = " + Integer.valueOf(id_subgraph).toString() + " and s2.db_id = s1.db_id_edge;";
edgeStatement = connection.prepare(request,false);
while(edgeStatement.step()) {
int db_id_edge = edgeStatement.columnInt(0);
String id_edge = edgeStatement.columnString(1);
Integer db_id_source = (Integer)edgeStatement.columnValue(2);
Integer db_id_target = (Integer)edgeStatement.columnValue(3);
if(db_id_source != null && db_id_target != null) {
StorableVertex source = null, target = null;
for(StorableVertex bufVertex : listVertex) {
if(bufVertex.getStorableId() == db_id_source) {
source = bufVertex;
}
if(bufVertex.getStorableId() == db_id_target) {
target = bufVertex;
}
}
if(source != null && target != null) {
StorableEdge e = new StorableEdge(db_id_edge, source, target, id_edge);
listEdge.add(e);
//attributes-----------------
SQLiteStatement attrStatement = null;
request = "select s2.db_id, s2.name, s2.value, s2.value_type " +
"from com_edge_attribute s1, attribute s2 " +
"where s1.db_id_edge = " + Integer.valueOf(db_id_edge).toString() + " and s2.db_id = s1.db_id_attribute;";
attrStatement = connection.prepare(request,false);
while(attrStatement.step()) {
int db_id_attr = attrStatement.columnInt(0);
String db_name = attrStatement.columnString(1);
String db_value = attrStatement.columnString(2);
String db_type = attrStatement.columnString(3);
e.addStorableAttribute(new StorableAttribute(db_id_attr, db_name, db_value, db_type));
}
attrStatement.dispose();
} else {
VisualGraph.log.printError("[" + this.getClass().getName() + ".getStorableGraph] [BAD] Source edge = null || target edge = null.(" + db_id_source + "," + db_id_target + ")");
}
}
}
edgeStatement.dispose();
//build subgraph-----------------
StorableSubGraph ssg = new StorableSubGraph(id_subgraph, id, name, listVertex, listEdge, directed);
subGraphs.add(ssg);
}
//getting of graph-------------------
SQLiteStatement graphStatement = null;
request = "select * " +
"from graph s1 " +
"where s1.db_id = " + Integer.valueOf(graphId).toString() + ";";
graphStatement = connection.prepare(request,false);
StorableGraph sg = null;
if(graphStatement.step()) {
Integer rootKey = graphStatement.columnInt(1);
String name = graphStatement.columnString(2);
sg = new StorableGraph(graphId, name, subGraphs, rootKey);
} else {
sg = new StorableGraph(graphId, "Untitle", subGraphs, null);
}
graphStatement.dispose();
return(sg);
}
});
StorableGraph result = job.complete();
Throwable ex = job.getError();
if (ex != null) {
VisualGraph.log.printError("[" + this.getClass().getName() + ".getStorableGraph] [BAD] Getting of graph. Exception : " + ex.getMessage());
VisualGraph.log.printException(ex);
VisualGraph.windowMessage.warningMessage("Exception : " + ex.getMessage(), "Get graph");
return null;
}
return(result);
}
/**
* This method returns storable subgraph.
* @param subGraphId - id of subgraph in data base.
* @return storable subgraph.
*/
public synchronized StorableSubGraph getStorableSubGraph(final int subGraphId) {
SQLiteJob<StorableSubGraph> job = this.currConnection.execute(new SQLiteJob<StorableSubGraph>() {
protected StorableSubGraph job(SQLiteConnection connection)
throws Throwable {
//getting of subgraph------------
int id_subgraph = subGraphId;
String request = "";
boolean directed = false;
String id = null;
String name = null;
SQLiteStatement subgraphStatement = null;
request = "select *" +
"from subgraph s1 " +
"where s1.db_id = " + Integer.valueOf(id_subgraph).toString() + ";";
subgraphStatement = connection.prepare(request,false);
if(subgraphStatement.step()) {
String buf = subgraphStatement.columnString(3);
if(buf != null && buf.equals("true")) {
directed = true;
} else {
directed = false;
}
id = subgraphStatement.columnString(1);
name = subgraphStatement.columnString(2);
}
subgraphStatement.dispose();
//vertexes-----------------------
ArrayList<StorableVertex>listVertex = new ArrayList<StorableVertex>();
SQLiteStatement vertexStatement = null;
request = "select s2.db_id, s2.id, s2.db_id_inner_graph " +
"from com_subgraph_vertex s1, vertex s2 " +
"where s1.db_id_subgraph = " + Integer.valueOf(id_subgraph).toString() + " and s1.db_id_vertex = s2.db_id;";
vertexStatement = connection.prepare(request,false);
while(vertexStatement.step()) {
int db_id_vertex = vertexStatement.columnInt(0);
String id_vertex = vertexStatement.columnString(1);
Integer db_id_innder_graph = (Integer)vertexStatement.columnValue(2);
StorableVertex v = new StorableVertex(db_id_vertex, id_vertex);
v.setInnerGraph(db_id_innder_graph);
listVertex.add(v);
//attributes-----------------
SQLiteStatement attrStatement = null;
try {
request = "select s2.db_id, s2.name, s2.value, s2.value_type " +
"from com_vertex_attribute s1, attribute s2 " +
"where s1.db_id_vertex = " + Integer.valueOf(db_id_vertex).toString() + " and s1.db_id_attribute = s2.db_id;";
attrStatement = connection.prepare(request,false);
while(attrStatement.step()) {
int db_id_attr = attrStatement.columnInt(0);
String db_name = attrStatement.columnString(1);
String db_value = attrStatement.columnString(2);
String db_type = attrStatement.columnString(3);
v.addStorableAttribute(new StorableAttribute(db_id_attr, db_name, db_value, db_type));
}
} catch(SQLiteException ex) {
VisualGraph.log.printException(ex);
} finally {
if(attrStatement != null) {
attrStatement.dispose();
}
}
}
vertexStatement.dispose();
//edges--------------------------
ArrayList<StorableEdge>listEdge = new ArrayList<StorableEdge>();
SQLiteStatement edgeStatement = null;
request = "select s2.db_id, s2.id, s2.db_id_source, db_id_target " +
"from com_subgraph_edge s1, edge s2 " +
"where s1.db_id_subgraph = " + Integer.valueOf(id_subgraph).toString() + " and s1.db_id_edge = s2.db_id;";
edgeStatement = connection.prepare(request,false);
while(edgeStatement.step()) {
int db_id_edge = edgeStatement.columnInt(0);
String id_edge = edgeStatement.columnString(1);
Integer db_id_source = (Integer)edgeStatement.columnValue(2);
Integer db_id_target = (Integer)edgeStatement.columnValue(3);
if(db_id_source != null && db_id_target != null) {
StorableVertex source = null, target = null;
for(StorableVertex bufVertex : listVertex) {
if(bufVertex.getStorableId() == db_id_source) {
source = bufVertex;
}
if(bufVertex.getStorableId() == db_id_target) {
target = bufVertex;
}
}
if(source != null && target != null) {
StorableEdge e = new StorableEdge(db_id_edge, source, target, id_edge);
listEdge.add(e);
//attributes-----------------
SQLiteStatement attrStatement = null;
try {
request = "select s2.db_id, s2.name, s2.value, s2.value_type " +
"from com_edge_attribute s1, attribute s2 " +
"where s1.db_id_edge = " + Integer.valueOf(db_id_edge).toString() + " and s1.db_id_attribute = s2.db_id;";
attrStatement = connection.prepare(request,false);
while(attrStatement.step()) {
int db_id_attr = attrStatement.columnInt(0);
String db_name = attrStatement.columnString(1);
String db_value = attrStatement.columnString(2);
String db_type = attrStatement.columnString(3);
e.addStorableAttribute(new StorableAttribute(db_id_attr, db_name, db_value,db_type));
}
} catch (SQLiteException ex) {
VisualGraph.log.printException(ex);
} finally {
if(attrStatement != null) {
attrStatement.dispose(); }
}
} else {
VisualGraph.log.printError("[" + this.getClass().getName() + ".getStorableSubGraph] [BAD] Source edge = null || target edge = null.(" + db_id_source + "," + db_id_target + ")");
}
}
}
edgeStatement.dispose();
//build subgraph-----------------
StorableSubGraph ssg = new StorableSubGraph(id_subgraph, id, name, listVertex, listEdge, directed);
return(ssg);
}
});
StorableSubGraph result = job.complete();
Throwable ex = job.getError();
if (ex != null)
{
VisualGraph.log.printException(ex);
return null;
}
return result;
}
/**
* This method returns storable subgraph.
* @param vertexId - list of vertex id's.
*/
public synchronized StorableSubGraph getStorableSubGraph(final ArrayList<Integer> vertexId) {
if(vertexId != null && vertexId.size() > 0) {
final ArrayList<StorableVertex>vertexes = new ArrayList<StorableVertex>();
final HashMap<Integer, StorableVertex>mapVertexes = new HashMap<Integer, StorableVertex>();
for(Integer buf : vertexId) {
StorableVertex v = this.getStorableVertex(buf);
if(v != null) {
vertexes.add(v);
mapVertexes.put(buf, v);
}
}
//get subgraph
StringBuffer request = new StringBuffer(1024);
request.append("select s1.db_id, s1.id, s1.name, s1.directed from subgraph s1, com_subgraph_vertex s2 where s1.db_id = s2.db_id_subgraph and s2.db_id_vertex = ");
request.append(vertexId.get(0));
request.append(";");
SQLiteStatement resultSubGraph = null;
SQLiteJob<StorableSubGraph> job = this.currConnection.execute(new SQLiteJob<StorableSubGraph>() {
protected StorableSubGraph job(SQLiteConnection connection) throws Throwable {
StringBuffer request = new StringBuffer(1024);
request.append("select s1.db_id, s1.id, s1.name, s1.directed from subgraph s1, com_subgraph_vertex s2 where s1.db_id = s2.db_id_subgraph and s2.db_id_vertex = ");
request.append(vertexId.get(0));
request.append(";");
SQLiteStatement currStatement = connection.prepare(request.toString(), false);
if (!currStatement.step()) {
currStatement.dispose();
return null;
}
int db_id_sg = currStatement.columnInt(0);
String id_sg = currStatement.columnString(1);
String name_sg = currStatement.columnString(2);
String buf = currStatement.columnString(3);
boolean direct_sg = false;
if (buf.toLowerCase().equals("true")) {
direct_sg = true;
}
currStatement.dispose();
request.setLength(0);
request.append("select s1.db_id, s1.id, s1.db_id_source, s1.db_id_target from edge s1, com_subgraph_edge s2 where s1.db_id = s2.db_id_edge and s2.db_id_subgraph = ");
request.append(db_id_sg);
request.append(";");
ArrayList<StorableEdge>edges = new ArrayList<StorableEdge>();
currStatement = connection.prepare(request.toString(), false);
while (currStatement.step()) {
int db_id_edge = currStatement.columnInt(0);
String id_edge = currStatement.columnString(1);
int db_source = currStatement.columnInt(2);
int db_target = currStatement.columnInt(3);
if(vertexId.contains(db_source) && vertexId.contains(db_target)) {
StorableEdge e = new StorableEdge(db_id_edge, mapVertexes.get(db_source), mapVertexes.get(db_target), id_edge);
edges.add(e);
//add edge attributes
request.setLength(0);
request.append("select s1.db_id, s1.name, s1.value, s1.value_type from attribute s1, com_edge_attribute s2 where s1.db_id = s2.db_id_attribute and s2.db_id_edge = ");
request.append(db_id_edge);
request.append(";");
SQLiteStatement resultAttribute = connection.prepare(request.toString(), false);
while (resultAttribute.step()) {
int db_id_attr = resultAttribute.columnInt(0);
String name_attr = resultAttribute.columnString(1);
String value_attr = resultAttribute.columnString(2);
String type_attr = resultAttribute.columnString(3);
StorableAttribute attr = new StorableAttribute(db_id_attr, name_attr, value_attr,
type_attr);
e.addStorableAttribute(attr);
}
}
}
StorableSubGraph ssg = new StorableSubGraph(db_id_sg, id_sg, name_sg, vertexes, edges, direct_sg);
return(ssg);
}
});
StorableSubGraph ssg = job.complete();
if (job.getError() != null) {
VisualGraph.log.printError(job.getError().getMessage());
VisualGraph.windowMessage.errorMessage("Can't execute SQL request. It is developer's bug.",
"Search panel error");
}
return ssg;
}
return(null);
}
/**
* This method executes SQL requst.
* @param request - SQL request.
*/
public synchronized List<List<Object>> executeSQLRequest(final String request) {
SQLiteJob<List<List<Object>>> job = this.currConnection.execute(new SQLiteJob<List<List<Object>>>() {
protected List<List<Object>> job(SQLiteConnection connection) throws Throwable {
SQLiteStatement currStatement = connection.prepare(request, false);
List<List<Object>> datas = new ArrayList<List<Object>>();
while (currStatement.step()) {
ArrayList<Object> row = new ArrayList<Object>();
for (int i = 0; i < currStatement.columnCount(); i++) {
row.add(currStatement.columnValue(i));
}
datas.add(row);
}
currStatement.dispose();
return (datas);
}
});
List<List<Object>> datas = job.complete();
Throwable ex = job.getError();
if (ex != null) {
VisualGraph.log.printException(ex);
return null;
}
return(datas);
}
/**
* This methods returns root storable subgraph.
* @param graphId - id of root subgraph.
*/
public synchronized StorableSubGraph getStorableRootSubGraph(final int graphId) {
SQLiteJob<Integer> job = this.currConnection.execute(new SQLiteJob<Integer>() {
protected Integer job(SQLiteConnection connection)
throws Throwable {
SQLiteStatement currStatement = null;
String request = "select * " +
"from graph s1 " +
"where s1.db_id = " + Integer.valueOf(graphId).toString() + ";";
currStatement = connection.prepare(request,false);
if(currStatement.step()) {
Integer a = currStatement.columnInt(1);
currStatement.dispose();
return a;
}
return null;
}
});
Integer rootKey = job.complete();
Throwable ex = job.getError();
if (ex != null) {
VisualGraph.log.printException(ex);
return null;
}
if (rootKey == null)
return null;
return(this.getStorableSubGraph(rootKey));
}
public synchronized StorableVertex getStorableVertex(final int vertexId) {
StorableVertex v = null;
SQLiteJob<StorableVertex> job = this.currConnection.execute(new SQLiteJob<StorableVertex>() {
protected StorableVertex job(SQLiteConnection connection) throws Throwable {
String request = "select s1.db_id, s1.id, s1.db_id_inner_graph " + "from vertex s1 "
+ "where s1.db_id = " + Integer.valueOf(vertexId).toString() + ";";
SQLiteStatement currStatement = connection.prepare(request, false);
StorableVertex v = null;
if (currStatement != null && currStatement.step()) {
int db_id = currStatement.columnInt(0);
String id = currStatement.columnString(1);
Integer db_id_inner_graph = (Integer) currStatement.columnValue(2);
v = new StorableVertex(db_id, id);
v.setInnerGraph(db_id_inner_graph);
}
currStatement.dispose();
return v;
}
});
v = job.complete();
if (job.getError() != null) {
VisualGraph.log.printError(job.getError().getMessage());
VisualGraph.windowMessage.errorMessage("Can't execute SQL request. It is developer's bug.",
"Search panel error");
}
if (v != null) {
SQLiteJob<List<StorableAttribute>> attrJob = this.currConnection
.execute(new SQLiteJob<List<StorableAttribute>>() {
protected List<StorableAttribute> job(SQLiteConnection connection) throws Throwable {
String request = "select s2.db_id, s2.name, s2.value, s2.value_type "
+ "from com_vertex_attribute s1, attribute s2 " + "where s1.db_id_vertex = "
+ Integer.valueOf(vertexId).toString() + " and s2.db_id = s1.db_id_attribute;";
SQLiteStatement currStatement = connection.prepare(request);
List<StorableAttribute> attrs = new ArrayList<StorableAttribute>();
if (currStatement != null) {
while (currStatement.step()) {
int db_id_attr = currStatement.columnInt(0);
String name = currStatement.columnString(1);
String value = currStatement.columnString(2);
String type = currStatement.columnString(3);
StorableAttribute attr = new StorableAttribute(db_id_attr, name, value, type);
attrs.add(attr);
}
}
return attrs;
}
});
List<StorableAttribute> attrs = attrJob.complete();
for (StorableAttribute attr : attrs) {
v.addStorableAttribute(attr);
}
if (attrJob.getError() != null) {
VisualGraph.log.printError(job.getError().getMessage());
VisualGraph.windowMessage.errorMessage("Can't execute SQL request. It is developer's bug.",
"Search panel error");
}
}
return (v);
}
/**
* This methods closes connection with data base.
*/
public synchronized void close() {
if (!this.currConnection.isStopped()) {
this.currConnection.stop(true);
this.currConnection.stop(false);
}
}
}